from os import walk, getcwd
from os.path import join, dirname
from datetime import datetime
from mydb.odbc import base
from mydb.mysql import atable, base


import xlsxwriter, xlrd, csv
from xlrd import xldate_as_tuple
from codecs import open



def bill_read(year, month, col_len=20, row_len=100, sheet_ind=0):
	"This function reads data from the bill"
	#beginning of function suite
	
	cur_dir = getcwd()
	
	bill_dir = join(dirname(cur_dir), "bills")
	bill_file = join(bill_dir, str(year)+str(month)+".csv")
	
	#int_fields = [2]
	dec_fields = range(5,20)
	
	field_names = [
	'Класс ПО', 
	'Тип сервиса', 
	'Номер', 
	'ФИО', 
	'Комментарий', 
	'Общие затраты', 
	'GPRS', 
	'SMS', 
	'MMS', 
	'МТС', 
	'М. моб.', 
	'М. фикс.', 
	'МГ', 
	'МН', 
	'ВСР', 
	'МНР', 
	'Dialup трафик', 
	'Трафик (вх)', 
	'Трафик (исх)', 
	'Прочее'
	]
	
	csvfile=open(bill_file,'r','utf-8')
	csvreader=csv.reader(csvfile, delimiter=';', quotechar='"')

	col_inds=[]
	rows=[]
	
	heads=next(csvreader)
	
	for field_name in field_names:
		try:
			col_inds+=[heads.index(field_name)]
		except:
			col_inds+=[-1]

	for row in csvreader:
		temp=[year, month]
		for col in col_inds:
			if col>=0 and col_inds.index(col) in dec_fields:
				temp+=[float(row[col].replace(',','.'))]
			elif col>=0 :
				temp+=[row[col]]
			else:
				temp+=['']
		rows+=[temp]

	return rows

def bill_write(rows, table_name='mobiles2_bills'):
	"This function updates month data in the bill table"
	#beginning of function suite
	mobiles2_bills=atable()
	mobiles2_bills.name=table_name
	mobiles2_bills.delete([['year','=',rows[0][0]],['AND month','=',rows[0][1]]])
	
	mobiles2_bills.insert([
	
	'year', 
	'month', 
	'class_po',
	'tip_servisa',
	'nomer', 
	'fio', 
	'comment', 
	'total', 
	'GPRS', 
	'SMS', 
	'MMS', 
	'mts', 
	'm_mob', 
	'm_fix', 
	'mg', 
	'mn', 
	'vsr', 
	'mnr', 
	'dialup', 
	'traffic_vh', 
	'traffic_ish', 
	'prochee'
	], rows)
	#end of function suite

def list_read(col_len=10, row_len=65535, sheet_ind=0):
	"This function reads data from the list"
	#beginning of function suite
	
	cur_dir = getcwd()
	
	#rep_dir = join(dirname(cur_dir), "reports")
	#rep_file = join(rep_dir, str(year)+str(month)+".xlsx")
	
	list_dir = dirname(cur_dir)
	list_file = join(list_dir, "list.xls")
	
	dec_fields = [5]
	date_fields = [6,7]
	
	field_names = [
	
	'Номер',
	'Оператор',
	'ФИО',
	'Табельный номер',
	'Должность',
	'Лимит',
	'Дата начала использования',
	'Дата окончания использования'
	]
	
	#print(bill_file)
	
	book = xlrd.open_workbook(list_file)
	sheet = book.sheet_by_index(sheet_ind)
	heads=[]
	col_inds=[]
	rows=[]
	
	for col in range(col_len):
		heads+=[sheet.cell_value(0, col)]
	
	for field_name in field_names:
		try:
			col_inds+=[heads.index(field_name)]
		except:
			col_inds+=[-1]
	
	#print(col_inds)
	for row in range(1, row_len+1):
		try:
			sheet.cell_value(row, 0)
		except:
			pass
		else:
			temp=[]
			for col in col_inds:
				if col_inds.index(col) in date_fields:
					try:
						date_tuple=xldate_as_tuple(sheet.cell_value(row, col),0)
						temp+=[datetime(date_tuple[0], date_tuple[1], date_tuple[2])]
					except:
						temp+=[None]
				elif col>=0:
					temp+=[sheet.cell_value(row, col)]
				else:
					temp+=[None]
			rows+=[temp]
			#print(temp)
	#print(rows[0])
	#end of function suite
	return rows

def list_write(rows, table_name='mobiles2_list'):
	"This function updates data in the list table"
	#beginning of function suite
	mobiles2_list=atable()
	mobiles2_list.name=table_name
	mobiles2_list.delete()
	
	mobiles2_list.insert([
	'nomer', 
	'operator', 
	'fio',
	'tabnom',
	'position', 
	'fin_limit', 
	'date_in', 
	'date_out'
	], rows)
	#end of function suite

def report_read(year, month):
	"This function reads data from the database"
	#beginning of function suite
	db=base()
	rows_generator=db.generate("SELECT a.nomer, b.fio, b.position, b.tabnom, b.date_in, b.date_out, b.fin_limit, a.total - b.fin_limit, a.total, a.GPRS, a.SMS, a.MMS, a.mts, a.m_mob, a.m_fix, a.mg, a.mn, a.vsr, a.mnr, a.dialup, a.traffic_vh, a.traffic_ish, a.prochee FROM mobiles2_bills a, mobiles2_list b WHERE a.nomer=b.nomer AND a.year="+str(year)+" AND a.month="+str(month)+" AND (b.date_out IS NULL OR b.date_out>STR_TO_DATE('01/"+str(month)+"/"+str(year)+"', '%d/%m/%Y')) AND b.date_in<=LAST_DAY(STR_TO_DATE('01/"+str(month)+"/"+str(year)+"', '%d/%m/%Y')) ORDER BY b.fio, a.nomer asc")
	result=[]
	for row in rows_generator:
		result+=[row]
	return result

def dict_read():
	"This function reads data from the database"
	#beginning of function suite
	db=base()
	rows_generator=db.generate("SELECT b.fio, b.position, b.nomer FROM mobiles2_list b WHERE (b.date_out IS NULL OR b.date_out>CURRENT_DATE()) AND b.date_in<=CURRENT_DATE() ORDER BY b.fio, b.nomer asc")
	result=[]
	for row in rows_generator:
		result+=[row]
	return result


def dict_write(records):
	"This function writes generates xlsx phone book"
	cur_dir = getcwd()
	rep_dir = join(dirname(cur_dir), "reports")
	rep_file = join(rep_dir, "phonebook.xlsx")

	heads=['№ п/п', 'ФИО', 'Должность', 'Номер']
	book = xlsxwriter.Workbook(rep_file)
	sheet = book.add_worksheet()
	
	sheet.set_portrait()
	#Устанавливается размер бумаги A4
	sheet.set_paper(9)
	
	lh=len(heads)
	lr=len(records)
	date_inds=[4,5]

	format={}
	format['heads'] = book.add_format({'font_name': 'Times New Roman', 'font_size': 12, 'bold': True, 'align': 'left', 'text_wrap': 1, 'border':1})
	format['rows'] = book.add_format({'font_name': 'Times New Roman', 'font_size': 12, 'align': 'left', 'border':1, 'text_wrap': 1})
	format['date_value'] = book.add_format({'font_name': 'Times New Roman', 'font_size': 12, 'align': 'left', 'border':1, 'text_wrap': 1, 'num_format': 'dd/mm/yyyy'})
	
	for x in range(lh):
		sheet.write(0, x, heads[x], format['heads'])
	
	for y in range(lr):
		sheet.write(1+y, 0, 1+y, format['rows'])
		for z in range(lh):
			if z==0:
				sheet.write(1+y, z, 1+y, format['rows'])
			elif z in date_inds:
				sheet.write(1+y, z, records[y][z-1], format['date_value'])
			else:
				sheet.write(1+y, z, records[y][z-1], format['rows'])
	
	sheet.repeat_rows(0)
	#Устанавливается ширина колонок таблицы
	#Колонка 1(A)
	sheet.set_column(0,0, 5)
	#Колонка 2(B)
	sheet.set_column(1,1, 25)
	#Колонка 3(C)
	sheet.set_column(2,2, 35)
	#Колонка 4(D)
	sheet.set_column(3,3, 15)
	#Колонка 5(E)
	sheet.set_column(4,4, 10)
	#Колонка 6(F)
	sheet.set_column(5,5, 10)
	
	book.close()


def report_write(year, month, rows):
	"This function generates xlsx book of the report for the date"
	#beginning of function suite
	cur_dir = getcwd()
	rep_dir = join(dirname(cur_dir), "reports")
	rep_file = join(rep_dir, str(year)+str(month)+".xlsx")
	
	#Создается xlsx книга 
	book = xlsxwriter.Workbook(rep_file)
	#В книге создается лист
	sheet = book.add_worksheet('sheet')
	
	
	
	#Устанавливается ориентация страницы
	sheet.set_landscape()
	#Устанавливается размер бумаги A3
	sheet.set_paper(8)
	#Устанавливается выравнивание по горизонтали
	sheet.center_horizontally()
	#Устанавливаются границы печати
	sheet.set_margins(left=0.2, right=0.2, top=0.2, bottom=0.2)
	
	#Создается словарь данных
	data = {}
	#Создается словарь форматов
	format = {}
	
	
	
	#Наполняется словарь данных для заголовка
	data['top'] = 'Отчет о расходах на мобильную связь по номерам: '+str(month)+'-'+str(year)+' г.'
	#Наполняется словарь данных для шапки таблицы
	data['heads']=[[],[]]
	#Строка №1
	data['heads'][0]=[
	'№ п/п',
	'Номер', 
	'ФИО', 
	'Должность', 
	'Табельный номер', 
	'Дата начала использования', 
	'Дата окончания использования', 
	'Лимит',
	'К удержанию',
	'Общие затраты', 
	'GPRS', 
	'SMS', 
	'MMS', 
	'МТС', 
	'М. моб.', 
	'М. фикс.', 
	'МГ', 
	'МН', 
	'ВСР', 
	'МНР', 
	'Dialup трафик', 
	'Трафик (вх)', 
	'Трафик (исх)', 
	'Прочее']
	
	hl=len(data['heads'][0])
	ud_ind=data['heads'][0].index('К удержанию')
	date_inds=[5,6]
	money_inds=list(range(7,hl))
	
	
	#Строка №2
	data['heads'][1]=list(range(1,hl+1))
	
	#Наполняется словарь данных таблицы
	data['rows'] = rows
	dl=len(data['rows'])
	#Наполняется словарь форматов для заголовка
	format['top'] = book.add_format({'bold': True, 'font_name': 'Times New Roman', 'font_size': 12, 'underline': 1, 'align': 'center'})
	#Наполняется словарь форматов для шапки таблицы
	format['heads'] = [[],[]]
	#Строка №1
	format['heads'][0] = book.add_format({'font_name': 'Times New Roman', 'font_size': 10, 'bold': True, 'align': 'left', 'text_wrap': 1, 'border':1})
	#Строка №2
	format['heads'][1] = book.add_format({'font_name': 'Times New Roman', 'font_size': 10, 'align': 'center', 'border':1})
	
	#Наполняется словарь форматов для строки данных таблицы
	format['rows'] = book.add_format({'font_name': 'Times New Roman', 'font_size': 10, 'align': 'left', 'border':1, 'text_wrap': 1})
	
	format['marked'] = book.add_format({'font_name': 'Times New Roman', 'font_size': 10, 'align': 'left', 'border':1, 'text_wrap': 1, 'bg_color':'#C0C0C0'})
	
	format['date_value'] = book.add_format({'font_name': 'Times New Roman', 'font_size': 10, 'align': 'left', 'border':1, 'text_wrap': 1, 'num_format': 'dd/mm/yyyy'})
	
	format['lastrow'] = book.add_format({'bold': True, 'font_name': 'Times New Roman', 'font_size': 10, 'align': 'right', 'border':1, 'text_wrap': 1})
	
	format['sign'] = book.add_format({'bold': True, 'font_name': 'Times New Roman', 'font_size': 10, 'align': 'left', 'text_wrap': 1})
	
	format['money'] = book.add_format({'font_name': 'Times New Roman', 'font_size': 10, 'align': 'right', 'border':1, 'text_wrap': 1, 'num_format': '# ##0.00'})
	
	format['uderzh'] = book.add_format({'font_name': 'Times New Roman', 'font_size': 10, 'align': 'right', 'border':1, 'text_wrap': 1, 'num_format': '# ##0.00', 'bg_color':'#C0C0C0'}) 
	
	
	format['lastmoney'] = book.add_format({'bold': True, 'font_name': 'Times New Roman', 'font_size': 10, 'align': 'right', 'border':1, 'text_wrap': 1, 'num_format': '# ##0.00'})
	

	#На лист записывается заголовок таблицы
	sheet.merge_range(0,0,0,hl-1, data['top'], format['top'])
	#Через строку ниже записываются строки шапки таблицы
	for x in range(2):
		for y in range(hl):
			sheet.write(2+x, y, data['heads'][x][y], format['heads'][x])
	
	#Под шапкой записываются строки
	counter=1
	for z in range(dl):
		if data['rows'][z][ud_ind-1]>0:
			sheet.write(2+x+1+z, 0, counter, format['marked'])
		else:
			sheet.write(2+x+1+z, 0, counter, format['rows'])
		counter+=1
		for y in range(1,hl):
			if y == ud_ind and data['rows'][z][y-1]<0:
				sheet.write(2+x+1+z, y, 0, format['money'])
			elif y == ud_ind and data['rows'][z][y-1]>0:
				sheet.write(2+x+1+z, y, data['rows'][z][y-1], format['uderzh'])
			elif y in date_inds:
				sheet.write(2+x+1+z, y, data['rows'][z][y-1], format['date_value'])
			elif y in money_inds:
				sheet.write(2+x+1+z, y, data['rows'][z][y-1], format['money'])
			else:
				sheet.write(2+x+1+z, y, data['rows'][z][y-1], format['rows'])
			
		
		
		
	#Записывается Итого
	#Слово "ИТОГО:"

	total_ind = data['heads'][0].index('Общие затраты')
	
	sheet.write(2+x+dl+2, total_ind-3, 'ИТОГО:', format['rows'])
	
	#Подсчитывается сумма
	sum=0
	for row in data['rows']:
		sum+=row[total_ind-1]
	#Записывается сумма
	sheet.write(2+x+dl+2, total_ind, sum, format['lastmoney'])
	
	
	#Подсчитывается удержание
	uderzh=0
	for row in data['rows']:
		if row[ud_ind-1]>0:
			uderzh+=row[ud_ind-1]
	#Записывается удержание
	sheet.write(2+x+dl+2, ud_ind, uderzh, format['uderzh'])
	
	
	sheet.write(2+x+dl+2+4, 2, 'Нач. ОАСУП:', format['sign'])
	sheet.write(2+x+dl+2+4, 3, 'Толстоноженко И.А.', format['sign'])
	
	#Устанавливается повторение на каждой странице шапки таблицы (строк 3 и 4)
	sheet.repeat_rows(2,3)
	#Устанавливается ширина колонок таблицы
	#Колонка 1(A)
	sheet.set_column(0,0, 4.5)
	#Колонка 2(B)
	sheet.set_column(1,1, 10)
	#Колонка 3(C)
	sheet.set_column(2,2, 17)
	#Колонка 4(D)
	sheet.set_column(3,3, 22)
	#Колонка 5(E)
	sheet.set_column(4,4, 6)
	#Колонка 6(F)
	sheet.set_column(5,5, 8)
	#Колонка 7(G)
	sheet.set_column(6,6, 8)
	#Колонка 8(H)
	sheet.set_column(7,7, 6)
	#Колонка 9-10(I-J)
	sheet.set_column(8,9, 9)
	#Колонка 11-24(K-V)
	sheet.set_column(9,23, 6)
	#end of function suite
	
	#Книга сохраняется и закрывается
	book.close()
	
	return




	

bill_write(bill_read(2013,10))
list_write(list_read())
report_write(2013, 10, report_read(2013, 10))